# coding=gbk

from datetime import date, timedelta
from DataAccess.DBConnFactory import DBConnFactory
from Import.TradeImport import *
from Import.PositionImport import *
from Position.CalcDailyPosition import *
from Recover.PositionRecover import *
from Config.ImportConfig import *

def test_case5():
	
	conn = DBConnFactory().get_db_connection('PKEDB')
	cursor = conn.cursor()
	
	trade_record_dir = "D:/work/pke/testcases/case3/"
	
	#clear db data
	clear_all()
	print 'info, clear all data complete.'

	#add portfolio info entry
	sql_text = '''delete from portfolio_info where portfolio_id='SCTest' '''
	cursor.execute(sql_text)
	conn.commit()
	sql_text = '''insert into portfolio_info(portfolio_id,name,inception_date,inception_val,currency,manager,"type",cash_account) 
				values('SCTest','SCTest',TO_DATE('2011-09-25','yyyy-mm-dd'),1000000,'USD','XieBing','PUBLIC',0) '''
	#cursor.execute(sql_text)
	conn.commit()
	print 'info, add test portfolio info complete.'
	
	sql_text = ''' insert into position
				(ref_date,portfolio_id,ticker,security_type,amount,avg_cost_price,price_currency)
				 values(TO_DATE(:1,'yyyy-mm-dd'),:2,:3,:4,:5,:6,:7) '''
	v =			[('2011-09-25','SC028','9999999','CASH',1000000.0,1.0,'USD'),
				('2011-09-25','SC028','9999999','CASH',0.0,1.0,'HKD'),
				('2011-09-25','SC028','9999999','CASH',0.0,1.0,'CNY')]
	cursor.executemany(sql_text, v)
	conn.commit()
	print 'info, add initial position complete.'
	
	trade_date = date(2011, 9, 26)
	one_day = timedelta(days=1)
	sql_text = '''insert into transaction(trade_date,ticker,trade_type,portfolio_id,security_type,amount,cost_price,currency,market) 
				values(TO_DATE(:1,'yyyy-mm-dd'),:2,:3,:4,:5,:6,:7,:8,:9) '''
	v =			[('2011-09-26','600000','BUY','SC028','STOCK',500.00,8.40,'CNY',''),
				('2011-09-26','08102','SHORT SELLING','SC028','STOCK',1000.00,2.20,'HKD','HK')]
	cursor.executemany(sql_text, v)
	conn.commit()
	import_daily_trades(trade_date, trade_record_dir)
	print 'info, import 2011-09-26 trades complete.'
	
	update_position_driven_by_dividend(trade_date)
	update_position_driven_by_transaction(trade_date)
	update_position_driven_by_repo_expire(trade_date)
	print 'info, calculate 2011-09-26 position complete.'
	
	trade_date = trade_date + one_day
	sql_text = '''insert into transaction(trade_date,ticker,trade_type,portfolio_id,security_type,amount,cost_price,currency,market) 
				values(TO_DATE(:1,'yyyy-mm-dd'),:2,:3,:4,:5,:6,:7,:8,:9) '''
	v =			[('2011-09-27','600000','BUY','SC028','STOCK',250,8.50,'CNY',''),
				('2011-09-27','08102','SHORT SELLING','SC028','STOCK',2000,2.5,'HKD','HK')]
	cursor.executemany(sql_text, v)
	conn.commit()
	import_daily_trades(trade_date, trade_record_dir)
	print 'info, import 2011-09-27 trades complete.'
	
	update_position_driven_by_dividend(trade_date)
	update_position_driven_by_transaction(trade_date)
	update_position_driven_by_repo_expire(trade_date)
	print 'info, calculate 2011-09-27 position complete.'
	
	trade_date = trade_date + one_day
	sql_text = '''insert into transaction(trade_date,ticker,trade_type,portfolio_id,security_type,amount,cost_price,currency,market) 
				values(TO_DATE(:1,'yyyy-mm-dd'),:2,:3,:4,:5,:6,:7,:8,:9) '''
	v =			[('2011-09-28','600000','SELL','SC028','STOCK',300,8.50,'CNY',''),
				('2011-09-28','08102','SHORT COVERING','SC028','STOCK',1500,2.5,'HKD','HK')]
	cursor.executemany(sql_text, v)
	conn.commit()
	import_daily_trades(trade_date, trade_record_dir)
	print 'info, import 2011-09-28 trades complete.'
	
	update_position_driven_by_dividend(trade_date)
	update_position_driven_by_transaction(trade_date)
	update_position_driven_by_repo_expire(trade_date)
	print 'info, calculate 2011-09-28 position complete.'
	
	sql_text = '''delete from portfolio_info where portfolio_id='SCTest' '''
	cursor.execute(sql_text)
	conn.commit()	
	return